﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using JYH.Data;
using Newtonsoft.Json;
using JYH.Model;
using JYH.DataAccess;
using JYH.Framework;
using System.Data;
using JYH.Model.API;

namespace JYH.Business
{
    /// <summary>
    ///  WmsTradeSource
    /// </summary>
    public class WmsTradeSourceBLL : BusinessBase<WmsTradeSource>
    {
        /// <summary>
        ///  WmsTradeSource
        /// </summary>
        public WmsTradeSourceBLL()
        {
            myDataBase = DB.D();
            this.SqlSelect = @";Select WmsTradeSource.*,BasMaterialInfo.MaterialNO,BasMaterialInfo.MaterialName,
                                           BasMaterialInfo.MaterialDesc,WmsTradeOrderInfo.TradeOrderNO,
                                           WmsTradeOrderInfo.FromWarehouseID,FW.WarehouseNO,FW.WarehouseName,
                                           TW.WarehouseID ToWarehouseID,TW.WarehouseNO ToWarehouseNO,TW.WarehouseName ToWarehouseName,
                                           InStr.StorageNO StorageNO,OutStr.StorageNO ToStorageNO
                                           From WmsTradeSource
                                           inner join WmsTradeOrderInfo on WmsTradeSource.TradeOrderID=WmsTradeOrderInfo.TradeOrderID
                                           Left join BasWarehouseInfo FW on FW.WarehouseID=WmsTradeOrderInfo.FromWarehouseID
                                           left join BasWarehouseInfo TW on TW.WarehouseID=WmsTradeOrderInfo.ToWarehouseID
                                           inner join BasMaterialInfo on WmsTradeSource.MaterialID=BasMaterialInfo.MaterialID
                                           left join BasStorageInfo InStr on WmsTradeSource.StorageID=InStr.StorageID
										  left join BasStorageInfo OutStr on WmsTradeSource.ToStorageID=OutStr.StorageID";
        }

        /// <summary>
        ///  调拨明细导入
        /// </summary>
        /// <param name="table"></param>
        /// <param name="tradeOrderID"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public bool DBFileDataTable(DataTable table, string tradeOrderID, ref ResponseJson<WmsTradeSource> message)
        {
            Database db = DB.D();
            db.BeginTransaction();
            StringBuilder sb = new StringBuilder();
            Sql sql = new Sql();
            string Msg = string.Empty;
            List<WmsTradeSource> WmsTradeSources = new List<WmsTradeSource>();
            try
            {
                string MaterialNO = "";
                int MIndex = -1;
                string StorageNO = "'',";
                int StrIndex = -1;
                if (table == null || table.Rows.Count == 0)
                {
                    message.Code = 500;
                    message.Message = "Excel is null";
                    db.AbortTransaction();
                    return false;
                }
                foreach (DataRow row in table.Rows)
                {
                    MaterialNO += "'" + row["物料料号"]?.ToString().Trim() + "',";
                    if (string.IsNullOrEmpty(row["调出储位"]?.ToString()))
                    {
                        message.Message = ErrorCode.WarehouseInfo01.GetMessage("OutStorageNO");
                        db.AbortTransaction();
                        return false;
                    }
                    if (string.IsNullOrEmpty(row["调入储位"]?.ToString()))
                    {
                        message.Message = ErrorCode.WarehouseInfo01.GetMessage("InStorageNO");
                        db.AbortTransaction();
                        return false;
                    }
                    StorageNO += "'" + row["调出储位"]?.ToString().Trim() + "',";
                    StorageNO += "'" + row["调入储位"]?.ToString().Trim() + "',";
                }
                MaterialNO = MaterialNO.Substring(0, MaterialNO.Length - 1);
                StorageNO = StorageNO.Substring(0, StorageNO.Length - 1);
                //获取物料列表
                var materialList = new BasMaterialInfoBLL().NotPagedQuery(new Sql().Where(" BasMaterialInfo.MaterialNO in (" + MaterialNO + ")"), out Msg, "BasMaterialInfo.MaterialNO");
                if (!materialList.Any())
                {
                    message.Message = ErrorCode.WarehouseInfo01.GetMessage("MaterialNO");
                    db.AbortTransaction();
                    return false;
                }
                //获取储位列表
                var storageList = new BasStorageInfoBLL().NotPagedQuery(new Sql().Where(" BasStorageInfo.StorageNO in (" + StorageNO + ")"), out Msg, "BasStorageInfo.StorageNO");
                if (!storageList.Any())
                {
                    message.Message = ErrorCode.WarehouseInfo01.GetMessage("StorageNO");
                    db.AbortTransaction();
                    return false;
                }
                var TradeOrderInfo = new WmsTradeOrderInfoBLL().GetSingeModel(new Sql().Where(" WmsTradeOrderInfo.tradeOrderID = '" + tradeOrderID + "'"));
                if (TradeOrderInfo == null)
                {
                    message.Message = ErrorCode.WarehouseInfo01.GetMessage("TradeOrderNO");
                    db.AbortTransaction();
                    return false;
                }
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    MIndex = -1;
                    WmsTradeSource wmsRecive = new WmsTradeSource();
                    MIndex = materialList.FindIndex(x => x.MaterialNO == table.Rows[i]["物料料号"]?.ToString().Trim());
                    if (MIndex > -1)
                    {
                        wmsRecive.MaterialID = materialList[MIndex].MaterialID;
                    }
                    else
                    {
                        message.Message = ErrorCode.WarehouseInfo01.GetMessage("Line" + (i + 1) + ",MaterialNO");
                        db.AbortTransaction();
                        return false;
                    }
                    if (materialList[MIndex].RState == "99")
                    {
                        message.Message = ErrorCode.Delete.GetMessage("Line" + (i + 1) + ",MaterialNO");
                        db.AbortTransaction();
                        return false;
                    }

                    if (WmsTradeSources.FindIndex(x => x.MaterialID == materialList[MIndex].MaterialID) > -1)
                    {
                        message.Message = ErrorCode.LamplInfo01.GetMessage("Line" + (i + 1) + ",MaterialNO");
                        db.AbortTransaction();
                        return false;
                    }
                    StrIndex = -1;
                    StrIndex = storageList.FindIndex(x => x.StorageNO == table.Rows[i]["调出储位"]?.ToString().Trim());
                    if (StrIndex < 0 || !string.Equals(TradeOrderInfo.FromWarehouseID, storageList[StrIndex].WarehouseID))
                    {
                        message.Message = ErrorCode.WarehouseInfo01.GetMessage("Line" + (i + 1) + ",OutStorageNO");
                        db.AbortTransaction();
                        return false;
                    }

                    wmsRecive.StorageID = storageList[StrIndex].StorageID;
                    StrIndex = -1;
                    StrIndex = storageList.FindIndex(x => x.StorageNO == table.Rows[i]["调入储位"]?.ToString().Trim());
                    if (StrIndex < 0 || !string.Equals(TradeOrderInfo.ToWarehouseID, storageList[StrIndex].WarehouseID))
                    {
                        message.Message = ErrorCode.WarehouseInfo01.GetMessage("Line" + (i + 1) + ",InStorageNO");
                        db.AbortTransaction();
                        return false;
                    }
                    wmsRecive.ToStorageID = storageList[StrIndex].StorageID;

                    wmsRecive.TradeOrderID = TradeOrderInfo.TradeOrderID;
                    //wmsRecive.FromWarehouseID
                    wmsRecive.TradeSourceID = Guid.NewGuid().ToString();
                    decimal rQty = 0; // 默认值
                    string rvalue = table.Rows[i]["需求数量"]?.ToString().Trim();
                    if (!string.IsNullOrWhiteSpace(rvalue) && decimal.TryParse(rvalue, out rQty))
                    {
                        // 成功转换，可以使用 receiveQty
                        wmsRecive.RequestQty = rQty;
                    }
                    else
                    {
                        // 转换失败处理，例如记录日志或提示错误信息
                        message.Message = ErrorCode.Format.GetMessage("Line" + (i + 1) + ",Qty");
                        db.AbortTransaction();
                        return false;
                    }

                    if (!string.IsNullOrWhiteSpace(table.Rows[i]["物料批号"]?.ToString().Trim()))
                    {
                        wmsRecive.BatchNO = table.Rows[i]["物料批号"]?.ToString().Trim();
                    }
                    wmsRecive.CreateUser = SysUserInfoBLL.GetCookieUser().UserName;
                    WmsTradeSources.Add(wmsRecive);
                    message.TotalCount += 1;
                }

                foreach (WmsTradeSource item in WmsTradeSources)
                {
                    sb.Append(BusinessTool.GetInsertSql(item));
                }
                db.Execute($"Delete from WmsTradeSource where WmsTradeSource.TradeOrderID='{tradeOrderID}'");
                if (sb.Length > 0)
                {
                    db.Execute(sb.ToString());
                }
            }
            catch (Exception ex)
            {
                message.Message = ex.Message;
                db.AbortTransaction();
                return false;
            }
            db.CompleteTransaction();
            return true;
        }

        /// <summary>
        ///  备料明细导入
        /// </summary>
        /// <param name="table"></param>
        /// <param name="tradeOrderID"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public bool MPFileDataTable(DataTable table, string tradeOrderID, ref ResponseJson<WmsTradeSource> message)
        {
            Database db = DB.D();
            db.BeginTransaction();
            StringBuilder sb = new StringBuilder();
            Sql sql = new Sql();
            string Msg = string.Empty;
            List<WmsTradeSource> WmsTradeSources = new List<WmsTradeSource>();
            try
            {
                string MaterialNO = "";
                int MIndex = -1;
                if (table == null || table.Rows.Count == 0)
                {
                    message.Code = 500;
                    message.Message = "Excel is null";
                    db.AbortTransaction();
                    return false;
                }
                for (int i = 3; i < table.Rows.Count; i++)
                {
                    MaterialNO += "'" + table.Rows[i][1]?.ToString().Trim() + "',";
                }
                //foreach (DataRow row in table.Rows)
                //{
                //    MaterialNO += "'" + row["物料料号"]?.ToString().Trim() + "',";
                //}
                MaterialNO = MaterialNO.Substring(0, MaterialNO.Length - 1);
                //获取物料列表
                var materialList = new BasMaterialInfoBLL().NotPagedQuery(new Sql().Where(" BasMaterialInfo.MaterialNO in (" + MaterialNO + ")"), out Msg, "BasMaterialInfo.MaterialNO");
                if (!materialList.Any())
                {
                    message.Message = ErrorCode.WarehouseInfo01.GetMessage("MaterialNO");
                    db.AbortTransaction();
                    return false;
                }

                var TradeOrderInfo = new WmsTradeOrderInfoBLL().GetSingeModel(new Sql().Where(" WmsTradeOrderInfo.tradeOrderID = '" + tradeOrderID + "'"));
                if (TradeOrderInfo == null)
                {
                    message.Message = ErrorCode.WarehouseInfo01.GetMessage("TradeOrderNO");
                    db.AbortTransaction();
                    return false;
                }
                for (int i = 3; i < table.Rows.Count; i++)
                {
                    MIndex = -1;
                    WmsTradeSource wmsRecive = new WmsTradeSource();
                    MIndex = materialList.FindIndex(x => x.MaterialNO == table.Rows[i][1]?.ToString().Trim());
                    if (MIndex > -1)
                    {
                        wmsRecive.MaterialID = materialList[MIndex].MaterialID;
                    }
                    else
                    {
                        message.Message = ErrorCode.WarehouseInfo01.GetMessage($"Line:'{i + 1}',MaterialNO");
                        db.AbortTransaction();
                        return false;
                    }
                    if (materialList[MIndex].RState == "99")
                    {
                        message.Message = ErrorCode.Delete.GetMessage($"Line:'{i + 1}',MaterialNO");
                        db.AbortTransaction();
                        return false;
                    }

                    if (WmsTradeSources.FindIndex(x => x.MaterialID == materialList[MIndex].MaterialID) > -1)
                    {
                        message.Message = ErrorCode.LamplInfo01.GetMessage($"Line:'{i + 1}',MaterialNO");
                        db.AbortTransaction();
                        return false;
                    }

                    wmsRecive.TradeOrderID = TradeOrderInfo.TradeOrderID;
                    //wmsRecive.FromWarehouseID
                    wmsRecive.TradeSourceID = Guid.NewGuid().ToString();
                    decimal rQty = 0; // 默认值
                    string rvalue = table.Rows[i][2]?.ToString().Trim();
                    if (!string.IsNullOrWhiteSpace(rvalue) && decimal.TryParse(rvalue, out rQty))
                    {
                        // 成功转换，可以使用 receiveQty
                        wmsRecive.RequestQty = rQty;
                    }
                    else
                    {
                        // 转换失败处理，例如记录日志或提示错误信息
                        message.Message = ErrorCode.Format.GetMessage($"Line:'{i + 1}',Qty");
                        db.AbortTransaction();
                        return false;
                    }
                    rQty = 0; // 默认值
                    rvalue = table.Rows[i][3]?.ToString().Trim();
                    if (!string.IsNullOrWhiteSpace(rvalue) && decimal.TryParse(rvalue, out rQty))
                    {
                        // 成功转换，可以使用 receiveQty
                        wmsRecive.UnitQty = rQty;
                    }
                    else
                    {
                        // 转换失败处理，例如记录日志或提示错误信息
                        message.Message = ErrorCode.Format.GetMessage($"Line:'{i + 1}',Qty");
                        db.AbortTransaction();
                        return false;
                    }
                    if (!string.IsNullOrWhiteSpace(table.Rows[i][0]?.ToString().Trim()))
                    {
                        wmsRecive.StationNO = table.Rows[i][0]?.ToString().Trim();
                    }
                    wmsRecive.CreateUser = SysUserInfoBLL.GetCookieUser().UserName;
                    // 假设table是DataTable，判断第一行第一列(索引[0][0])
                    if (table.Rows[i][4] == DBNull.Value || table.Rows[i][4] == null || string.IsNullOrWhiteSpace(table.Rows[i][4].ToString()) ||
                        table.Rows[i][4].ToString() == "0")
                    {
                        // 单元格为空或为0的处理逻辑
                        wmsRecive.IsLead = 0;
                    }
                    else
                    {
                        wmsRecive.IsLead = 1;
                    }
                    if (table.Rows[i][5] == DBNull.Value || table.Rows[i][5] == null || string.IsNullOrWhiteSpace(table.Rows[i][5].ToString()) ||
                       table.Rows[i][5].ToString() == "0")
                    {
                        // 单元格为空或为0的处理逻辑
                        wmsRecive.IsTinning = 0;
                    }
                    else
                    {
                        wmsRecive.IsTinning = 1;
                    }
                    WmsTradeSources.Add(wmsRecive);
                    message.TotalCount += 1;
                }

                foreach (WmsTradeSource item in WmsTradeSources)
                {
                    sb.Append(BusinessTool.GetInsertSql(item));
                }
                db.Execute($"Delete from WmsTradeSource where WmsTradeSource.TradeOrderID='{tradeOrderID}'");
                if (sb.Length > 0)
                {
                    db.Execute(sb.ToString());
                }
            }
            catch (Exception ex)
            {
                message.Message = ex.Message;
                db.AbortTransaction();
                return false;
            }
            db.CompleteTransaction();
            return true;
        }

        /// <summary>
        /// 库存备料方法
        /// </summary>
        public bool PrepareMaterialForOrder(LigthList<WmsTradeSource> Info, ref ResponseJson<WmsTradeSource> responseJson)
        {
            Database db = DB.D();
            db.BeginTransaction();
            StringBuilder sb = new StringBuilder();
            List<WmsTradeSource> TradeSourceList = new List<WmsTradeSource>();
            try
            {
                // 1. 查询调拨单信息
                Sql sql = new Sql();
                sql.Append($"SELECT * FROM WmsTradeOrderInfo WHERE TradeOrderID ='{Info.OrderNO}'");
                var tradeOrder = db.FirstOrDefault<WmsTradeOrderInfo>(sql);

                if (tradeOrder == null)
                {
                    responseJson.Message = ErrorCode.WarehouseInfo01.GetMessage("TradeOrderNO");
                    return false;
                }

                if (tradeOrder.RState != "1")
                {
                    responseJson.Message = ErrorCode.Admin.GetMessage();
                    return false;
                }
                //检索sql；
                string SqlSelects = "";
                WmsStockBarcodeBLL stockBarcodeBLL = new WmsStockBarcodeBLL();
                // 2. 查询调拨明细 true=All
                WmsTradeSourceBLL tradeSourceBLL = new WmsTradeSourceBLL();
                sql = new Sql();
                sql.Append(tradeSourceBLL.SqlSelect);
                sql.Where($"WmsTradeOrderInfo.TradeOrderID = '{Info.OrderNO}' AND WmsTradeOrderInfo.RState = '1'");
                TradeSourceList = db.Fetch<WmsTradeSource>(sql);
                if (Info.LigthCheck)
                {
                    SqlSelects = stockBarcodeBLL.SqlSelect;
                }
                else
                {
                    SqlSelects = @"Select top 1 WmsStockBarcode.*,BasBarcodeInfo.BarcodeNO,BasBarcodeInfo.BatchNO,
                                             BasBarcodeInfo.IsLead,BasBarcodeInfo.IsTinning,
                                             BasMaterialInfo.MaterialNO,BasMaterialInfo.MaterialDesc,BasMaterialInfo.MaterialName,
                                             BasWarehouseInfo.WarehouseNO,BasWarehouseInfo.WarehouseName,BasLampInfo.LampNO,
                                             BasLampInfo.LampCode,BasRackInfo.RackNO,BasStorageInfo.StorageNO
                                             From WmsStockBarcode
                                             inner join BasBarcodeInfo on WmsStockBarcode.BarcodeID=BasBarcodeInfo.BarcodeID
                                             inner join BasMaterialInfo on WmsStockBarcode.MaterialID=BasMaterialInfo.MaterialID
                                             inner join BasWarehouseInfo on WmsStockBarcode.WarehouseID=BasWarehouseInfo.WarehouseID
                                             left join BasLampInfo on WmsStockBarcode.LampID=BasLampInfo.LampID
                                             left join BasRackInfo on BasLampInfo.RackID=BasRackInfo.RackID
											 left JOIN BasStorageInfo on BasStorageInfo.StorageID=WmsStockBarcode.StorageID";
                }

                if (!TradeSourceList.Any())
                {
                    responseJson.Message = ErrorCode.WarehouseInfo01.GetMessage("WmsTradeSource");
                    return false;
                }

                // 3. 遍历每一行调拨明细，进行备料
                foreach (var source in TradeSourceList)
                {
                    if (source.RequestQty <= 0)
                        continue; // 跳过不需要备料的项

                    // 当前已发数量
                    decimal issuedQty = source.ActionQty ?? 0;

                    // 剩余需要备料的数量
                    decimal remainingQty = source.LackQty;

                    if (remainingQty <= 0)
                        continue; // 已满足需求，跳过

                    // 4. 查询符合条件的库存条码（FIFO + Qty 最小优先）

                    sql = new Sql();
                    sql.Append(SqlSelects);
                    sql.Where($"WmsStockBarcode.WarehouseID='{tradeOrder.FromWarehouseID}'");
                    sql.Where($"WmsStockBarcode.MaterialID='{source.MaterialID}'");
                    sql.Where($"WmsStockBarcode.IsLock='0'");
                    sql.Where($"BasBarcodeInfo.IsLead='{source.IsLead}'");
                    sql.Where($"BasBarcodeInfo.IsTinning='{source.IsTinning}'");
                    sql.OrderBy("BasBarcodeInfo.BatchNO ");     // FIFO：先批次再时间
                    sql.OrderBy("WmsStockBarcode.StockQty ");   // 同一批次内，小数量优先出库
                    List<WmsStockBarcode> stockBarcodes = db.Fetch<WmsStockBarcode>(sql);

                    if (!stockBarcodes.Any())
                    {
                        continue;
                    }
                    decimal selectedQty = 0;
                    List<WmsStockBarcode> selectedBarcodes = new List<WmsStockBarcode>();

                    // 5. 累加条码直到满足需求数量
                    foreach (var barcode in stockBarcodes)
                    {
                        selectedBarcodes.Add(barcode);
                        selectedQty += (decimal)barcode.StockQty;
                        if (selectedQty >= remainingQty)
                            break;
                    }
                    // 6. 构建 TradeBarcode 数据并插入数据库
                    foreach (var barcode in selectedBarcodes)
                    {
                        WmsTradebarcode tradeBarcode = new WmsTradebarcode
                        {
                            TradebarcodeID = Guid.NewGuid().ToString(),
                            TradeOrderID = tradeOrder.TradeOrderID,
                            BarcodeID = barcode.BarcodeID,
                            StorageID = barcode.StorageID,
                            LampID = barcode.LampID,
                            Qty = barcode.StockQty,
                            StationNO = source.StationNO,
                            CreateUser = SysUserInfoBLL.GetCookieUser().UserName,
                            RState = "0" // 未发料
                        };

                        sb.Append(BusinessTool.GetInsertSql(tradeBarcode));
                    }

                    // 7. 更新 TradeSource 的 ActionQty（已发数量）
                    source.CalculateQty += selectedQty;

                    sb.Append(BusinessTool.GetUpdateSql(source));

                    // 8. 锁定这些库存条码（防止重复使用）
                    string lockedBarcodeIDs = string.Join(",", selectedBarcodes.Select(b => $"'{b.BarcodeID}'"));
                    sb.AppendFormat($"UPDATE WmsStockBarcode SET IsLock = 1 WHERE BarcodeID IN ({lockedBarcodeIDs}); ");
                }

                // 9. 执行 SQL
                if (sb.Length > 0)
                {
                    db.Execute(sb.ToString());
                }

                db.CompleteTransaction();
                responseJson.Message = "备料完成";
                return true;
            }
            catch (Exception ex)
            {
                db.AbortTransaction();
                responseJson.Message = ex.Message;
                return false;
            }
        }

        /// <summary>
        /// 保存领料来源明细
        /// </summary>
        /// <param name="info"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public bool SaveSouceItem(WmsTradeSource info, ref ResponseJson<WmsTradeSource> message)
        {
            Database db = DB.D();
            db.BeginTransaction();
            StringBuilder sb = new StringBuilder();
            Sql sql = new Sql();
            string Msg = string.Empty;
            try
            {
                // 1. 查询领料单信息
                sql = new Sql();
                sql.Append($"SELECT * FROM WmsTradeOrderInfo WHERE TradeOrderID ='{info.TradeOrderID}'");
                var tradeOrder = db.FirstOrDefault<WmsTradeOrderInfo>(sql);
                if (tradeOrder == null)
                {
                    message.Message = ErrorCode.WarehouseInfo01.GetMessage("TradeOrderNO");
                    db.AbortTransaction();
                    return false;
                }
                // 1. 库位校对
                sql = new Sql();
                sql.Append($"SELECT * from BasStorageInfo where StorageNO='{info.StorageNO}' and WarehouseID='{info.FromWarehouseID}'");
                var StorageInfo = db.FirstOrDefault<BasStorageInfo>(sql);
                if (StorageInfo == null)
                {
                    message.Message = ErrorCode.WarehouseInfo01.GetMessage(info.StorageNO);
                    db.AbortTransaction();
                    return false;
                }
                WmsTradeSource tradeSource = new WmsTradeSource();
                tradeSource.TradeSourceID = Guid.NewGuid().ToString();
                tradeSource.TradeOrderID = info.TradeOrderID;
                tradeSource.FromWarehouseID = info.FromWarehouseID;
                tradeSource.StorageID = StorageInfo.StorageID;
                tradeSource.MaterialID = info.MaterialID;
                tradeSource.BatchNO = info.BatchNO;
                tradeSource.RequestQty = info.RequestQty;
                tradeSource.CreateUser = SysUserInfoBLL.GetCookieUser().UserName;
                sb.Append(BusinessTool.GetInsertSql(tradeSource));
                if (sb.Length > 0)
                    db.Execute(sb.ToString());
            }
            catch (Exception ex)
            {
                message.Message = ex.Message;
                db.AbortTransaction();
                return false;
            }
            db.CompleteTransaction();
            return true;
        }
    }
}